IF OBJECT_ID('proc_CheckWireropePlan') IS NOT NULL
    DROP PROCEDURE proc_CheckWireropePlan;
GO
CREATE PROCEDURE proc_CheckWireropePlan
AS
BEGIN
    /***** 在用绳检测定时任务用 *****/

    SET NOCOUNT ON;

    --注释
    DECLARE @tb_UsingWirerope TABLE
    (
        id INT IDENTITY,
        usingWireropeID INT,
        code VARCHAR(50),
        groupInfoID INT,
        cnt INT,
        departmentID INT
    );


    DECLARE @now DATETIME = GETDATE(); --注释
    DECLARE @cnt INT; --定义变量，用于保存今天要处理的数据条数


    --添加今天要处理的在用绳检测的数据
    INSERT @tb_UsingWirerope
    (
        usingWireropeID,
        groupInfoID,
        code,
        cnt,
        departmentID
    )
    SELECT UsingWireropeID,
           GroupInfoID,
           Code,
           Cnt,
           DepartmentID
    FROM wi.UsingWirerope
    WHERE Status = 1
          AND cnt IS NOT NULL
          AND cnt > 0
          AND DATEDIFF(DAY, @now, NextCheckDate) = 7;


    --如果今天要处理的数量为0，则不再执行
    SELECT @cnt = COUNT(1)
    FROM @tb_UsingWirerope;

    IF @cnt = 0
    BEGIN
        RETURN;
    END;

    DECLARE @code_part1 VARCHAR(50) = 'JC' + RIGHT(CAST(DATEPART(YEAR, @now) AS VARCHAR), 2) + RIGHT('00' + CAST(DATEPART(MONTH, @now) AS VARCHAR), 2); --定义编码的前缀

    DECLARE @code_part2 INT = 0; --定义编码的数字位

    DECLARE @code_full VARCHAR(50); --定义完整编码

    --如果存在前缀的数据，则取出最后一条的编码的数字部分
    IF EXISTS
    (
        SELECT *
        FROM wi.CheckWirerope
        WHERE Code LIKE @code_part1 + '%'
    )
    BEGIN
        DECLARE @db_code VARCHAR(50);

        SELECT TOP 1
               @db_code = Code
        FROM wi.CheckWirerope
        WHERE Code LIKE @code_part1 + '%'
        ORDER BY Code DESC;

        SET @code_part2 = CAST(RIGHT(@db_code, 4) AS INT);
    END;


    DECLARE @i INT = 1; --检测记录用
    DECLARE @j INT; --绳子条数用

    DECLARE @usingWireropeID INT; --注释
    DECLARE @groupInfoID INT; --注释
    DECLARE @code VARCHAR(50); --注释
    DECLARE @departmentID INT; --注释
    DECLARE @cw_cnt INT; --注释

    DECLARE @checkWireropeID INT; --添加检测记录的id

    BEGIN TRANSACTION;
    BEGIN TRY

        WHILE @i <= @cnt
        BEGIN

            --先把当前循环的这条数据信息拿出来
            SELECT @usingWireropeID = usingWireropeID,
                   @groupInfoID = groupInfoID,
                   @code = code,
                   @cw_cnt = cnt,
                   @departmentID = departmentID,
                   @j  = 1
            FROM @tb_UsingWirerope
            WHERE id = @i;

            SET @code_full = @code_part1 + RIGHT('0000' + CAST(@code_part2 + @i AS VARCHAR), 4);

            --添加检测主表
            INSERT wi.CheckWirerope
            (
                GroupInfoID,
                Code,
                UsingWireropeID,
                Type,
                Status,
                IsSuggestChange,
                CreateUserID,
                CreateUserName,
                CreateDate
            )
            VALUES
            (
                @groupInfoID, -- GroupInfoID - int
                @code_full, -- Code 
                @usingWireropeID, -- UsingWireropeID - int
                1, --Type - int
                100, -- Status - int
                0, --IsSuggestChange - bit
                0, -- CreateUserID - int
                N'系统', -- CreateUserName - nvarchar(50)
                @now -- CreateDate - datetime
            );
            SELECT @checkWireropeID = @@IDENTITY;


            --循环添加在用绳检测详情
            WHILE @j <= @cw_cnt
            BEGIN
                INSERT wi.CheckWireropeDetail
                (
                    GroupInfoID,
                    UsingWireropeID,
                    CheckWireropeID,
                    SortNo
                )
                VALUES
                (
                    @groupInfoID, -- GroupInfoID - int
                    @usingWireropeID, -- UsingWireropeID - int
                    @checkWireropeID, -- CheckWireropeID - int
                    @j -- SortNo - int
                );

                SET @j += 1;
            END;


            --添加通知，根据当前绳子的矿山，去查角色（有在用绳检测模块且（角色部门中有该部门或角色设置的全部））的所有角色ID
            --再根据角色ID，得到哪些用户设置的这些角色，就发给这些用户
            INSERT rec.Message
            (
                GroupInfoID,
                Type,
                Title,
                ReceiveUserID,
                Data,
                Status,
                CreateDate
            )
            SELECT @groupInfoID,
                   2,
                   '在用绳' + @code_full + '检测',
                   T2.UserID,
                   '{"code":"' + @code_full + '"}',
                   0,
                   @now
            FROM
            (
                SELECT DISTINCT
                       ur.UserID
                FROM dbo.UserRole ur
                INNER JOIN
                (
                    --查询有40301这个功能的，且包含有当前绳对应矿山的角色id
                    SELECT rd.RoleID
                    FROM dbo.RoleDepartment rd
                    INNER JOIN dbo.RolePermission rp ON rp.RoleID = rd.RoleID
                    INNER JOIN dbo.Permission p ON p.PermissionID = rp.PermissionID
                    WHERE rd.DepartmentID = @departmentID
                          AND p.PerID = 40301
                          AND rd.GroupInfoID = @groupInfoID
                    UNION ALL

                    --查询有40301这个功能的，且角色设置的数据权限为全部的角色id
                    SELECT r.RoleID
                    FROM dbo.Role r
                    INNER JOIN dbo.RolePermission rp ON rp.RoleID = r.RoleID
                    INNER JOIN dbo.Permission p ON p.PermissionID = rp.PermissionID
                    WHERE r.DataPermissionType = 0
                          AND p.PerID = 40301
                          AND r.GroupInfoID = @groupInfoID
                ) AS T ON T.RoleID = ur.RoleID
                WHERE ur.GroupInfoID = @groupInfoID
            ) AS T2;


            SET @i += 1;
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        PRINT '出错';
        ROLLBACK TRANSACTION;
    END CATCH;

    SET NOCOUNT OFF;
END;
GO